package br.com.iqt;

import java.sql.*;
import java.util.ArrayList;
import java.util.StringTokenizer;

/**
 *
 * @author Arlino 
 */

public class ConnectionDbms{
    public static String message = "";
    
    private static final String driverPostgresql = "org.postgresql.Driver";
    private static final String driverSqlServer = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private static final String driverOracle = "oracle.jdbc.driver.OracleDriver";
    
    public static String getDatabaseName(int dbms){
        switch(dbms){
            case Dbms.POSTGRESQL:
                return "PostgreSQL";
            case Dbms.SQLSERVER:
                return "SQL Server";
            case Dbms.ORACLE:
                return "Oracle";
        }
        return null;
    }
    /*
    public static boolean isIndex(Dbms dbms, String scheme, String table, String column){
        String driver = "";
        String url = "";
        String sql = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                if(scheme == null || scheme.isEmpty()) {
                    scheme = "public";
                }
                sql = "select * from pg_index where indrelid = (select oid from pg_class where upper(relname)= upper('" + table
                        + "') and relkind = 'r' and relnamespace = (select oid from pg_namespace where UPPER(nspname) = "
                        + "UPPER('" + scheme + "'))) and indkey[0] = (select attnum from pg_attribute where upper(attname) = upper('" + column
                        + "') and attrelid = (select oid from pg_class where upper(relname) = upper('" + table + "') and relkind = 'r' "
                        + "and relnamespace = (select oid from pg_namespace where UPPER(nspname) = UPPER('" + scheme + "'))))";
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                sql = "select * from sys.index_columns where object_id = (select object_id from sys.objects "
                        + "where name = '" + table + "') and column_id = (select column_id from sys.columns "
                        + "where name = '" + column + "'and object_id = (select object_id from sys.objects "
                        + "where name = '" + table + "'))";
                //sql = sql.replace("databaseName", dbms.getDatabase());
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":orcl";
                sql = "select * from user_ind_columns where upper(table_name) = upper('" + table + "') and "
                        + "upper(column_name) = upper('" + column + "') and upper(index_name) IN (select "
                        + "upper(index_name) from user_indexes where upper(table_name) = upper('" + table + "'))";
                break;
        }
        
        try{
            Class.forName(driver);
            Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
            Statement stm = connection.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            boolean value = rs.next();
            stm.close();
            connection.close();
            return value;
        }
        catch (ClassNotFoundException ex){
            System.out.println("(ClassNotFoundException) Erro ao procurar índice. Classe ConectionDbms.isIndex(). Erro: " + ex.getMessage());
            return false;
        }
        catch (SQLException e){
            System.out.println("(SQLException) Erro ao procurar índice. Classe ConectionDbms.isIndex(). Erro: " + e.getMessage());
            System.out.println("Sql = " + sql);
            return false;
        }
    }
    
    public static boolean isPrimaryKey(Dbms dbms, String scheme, String table, String column) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        String sql = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                if(scheme == null || scheme.isEmpty())
                    scheme = "public";
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                sql = "select * from pg_constraint where contype='p' and conrelid = (select oid from pg_class where "
                        + "relname= '" + table + "'  and relkind = 'r' and relnamespace = (select oid from pg_namespace "
                        + "where UPPER(nspname) = UPPER('" + scheme + "'))) and conkey[1] = (select attnum from pg_attribute where attname = "
                        + "'" + column + "' and attrelid = (select oid from pg_class where relname= '" + table + "'))";
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                System.out.println("Procura por coluna com índice em uma determinada tabela ainda não foi implementado para esse SGBD! Classe ConnectionDbms.isIndex()");
                sql = "select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '"
                        + table + "' and COLUMN_NAME = '" + column + "' and CONSTRAINT_NAME = (select name "
                        + "from sys.key_constraints where parent_object_id = (select object_id from "
                        + "sys.tables where name = '" + table + "'))";
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":orcl";
                sql = "select * from user_cons_columns where upper(table_name) = upper('" + table + "') and "
                        + "upper(column_name) = upper('" + column + "') AND upper(constraint_name)  IN "
                        + "(select upper(constraint_name) from user_constraints where upper(table_name) = "
                        + "upper('" + table + "') and constraint_type = 'P')";
                break;
        }
            Class.forName(driver);
            Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
            Statement stm = connection.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            boolean value = rs.next();
            stm.close();
            connection.close();
            return value;
    }
    
    
    public static boolean hasForingkeyFromTable1InTabel2(Dbms dbms, String schema1, String table1, String schema2, String table2, String column) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        String sql = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                
                if(schema1 == null || schema1.isEmpty())
                    schema1 = "public";
                
                if(schema2 == null || schema2.isEmpty())
                    schema2 = "public";
                
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                sql = "select * from pg_constraint where contype='f' and conrelid = (select oid from pg_class where "
                        + "UPPER(relname)=UPPER('" + table2 + "') and relkind = 'r' and relnamespace = (select oid from "
                        + "pg_namespace where UPPER(nspname)=UPPER('" + schema2 +"'))) and confkey[1] = (select attnum from "
                        + "pg_attribute where UPPER(attname) = UPPER('" + column + "') and attrelid = (select oid from "
                        + "pg_class where UPPER(relname)=UPPER('" + table1 + "') and relkind = 'r' and relnamespace = "
                        + "(select oid from pg_namespace where UPPER(nspname) = UPPER('" + schema1 + "'))))";
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                sql = "select * from sys.foreign_key_columns where parent_object_id = (select object_id "
                        + "from sys.objects where name = '" + table2 + "') and referenced_object_id = "
                        + "(select object_id from sys.objects where name = '" + table1 + "') and "
                        + "referenced_column_id =(select column_id from sys.columns where name = '" 
                        + column + "')";
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":orcl";
                sql = "select * from "
                        + "user_cons_columns where constraint_name = (select constraint_name from user_"
                        + "constraints where table_name = 'LINEITEM' and constraint_type = 'R' AND r_"
                        + "constraint_name = (select constraint_name from user_cons_columns where "
                        + "upper(table_name) = upper('" + table1 + "') and upper(column_name) = "
                        + "upper('" + column + "') AND upper(constraint_name)  = (select upper("
                        + "constraint_name) from user_constraints where upper(table_name) = upper('" 
                        + table1 + "') and constraint_type = 'P')))";
        }
            Class.forName(driver);
            Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
            Statement stm = connection.createStatement();
            ResultSet rs = stm.executeQuery(sql);
            boolean value = rs.next();
            stm.close();
            connection.close();
            return value;
    }
    
    public static String getRuntime(Dbms dbms, String sql) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":orcl";
                break;
        }
        
        Class.forName(driver);
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement stm = connection.createStatement();
        
        long finalTime = 0;
        StringTokenizer stringTokenizer = new StringTokenizer(sql, ";");
        while(stringTokenizer.hasMoreElements()){
            String sqlAux = stringTokenizer.nextToken();
            String executionType;
            if(!sqlAux.toUpperCase().trim().startsWith("SELECT") || sqlAux.trim().toUpperCase().contains(" INTO "))
                executionType = "executeUpdate";
            else
                executionType = "executeQuery";
            
            long startTime = System.currentTimeMillis();
            if(executionType.equals("executeUpdate"))
                stm.executeUpdate(sqlAux);
            else
                stm.executeQuery(sqlAux);
            long endTime = System.currentTimeMillis();
            
            finalTime += endTime - startTime;
        }
        stm.close();
        connection.close();
        return (Long.toString(finalTime) + " ms");
    }
    
    public static ArrayList getExecutionPlan(Dbms dbms, String sql) throws ClassNotFoundException, SQLException{
        String driver, url;
        Connection connection;
        Statement statement;
        ResultSet resultSet;
        ArrayList list = new ArrayList();
        
        //try{
            switch(dbms.getDbms()){
                case Dbms.POSTGRESQL:
                    driver = ConnectionDbms.driverPostgresql;
                    Class.forName(driver);
                    url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                    sql = "EXPLAIN " + sql;
                    
                    connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
                    statement = connection.createStatement();
                    
                    resultSet = statement.executeQuery(sql);
                    while (resultSet.next()) {
                        list.add(resultSet.getString(1));
                    }
                    
                    connection.close();
                    return list;
                case Dbms.SQLSERVER:
                    driver = ConnectionDbms.driverSqlServer;
                    Class.forName(driver);
                    url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                    
                    connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
                    statement = connection.createStatement();
                    
                    statement.execute("SET SHOWPLAN_TEXT OFF");
                    statement.execute("SET SHOWPLAN_TEXT ON");
                    
                    resultSet = statement.executeQuery(sql);
                    while (resultSet.next()) {
                        list.add(resultSet.getString(1));
                    }
                    
                    statement.execute("SET SHOWPLAN_TEXT OFF");
                    connection.close();
                    return list;
                case Dbms.ORACLE:
                    driver = ConnectionDbms.driverOracle;
                    Class.forName(driver);
                    url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":orcl";
                    sql = "select operation, id from v$sql_plan sp, v$sql s where "
                            + "sp.sql_id = s.sql_id and sql_text = '" + sql + "'";
                    
                    connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
                    statement = connection.createStatement();
                    
                    statement.execute("SET SHOWPLAN_TEXT OFF");
                    statement.execute("SET SHOWPLAN_TEXT ON");
                    
                    resultSet = statement.executeQuery(sql);
                    
                    while (resultSet.next()) {
                        list.add(resultSet.getString("operation"));
                    }
                    
                    connection.close();
                    return list;
            }
            /*
        }
        catch (ClassNotFoundException ex){
            ConnectionDbms.message = "Falha na conexão " + ConnectionDbms.getDatabaseName(dbms.getDbms())
                    + ". Erro: " + ex.getMessage();
            return null;
        }
        catch (SQLException e){
            ConnectionDbms.message = "Falha na conexão " + ConnectionDbms.getDatabaseName(dbms.getDbms())
                    + ". Erro: " + e.getMessage();
            return null;
        }
        * 
        */
    //    return null;
    //}
    
    public static boolean testConnection(Dbms dbms){
        String driver = "";
        String url = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":hmg";
                break;
        }
        
        try{
            Class.forName(driver);
            Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
            connection.close();
            return true;
        }
        catch (ClassNotFoundException ex){
            ConnectionDbms.message = "Falha na conexão " + ConnectionDbms.getDatabaseName(dbms.getDbms()) 
                                    + ". Erro: " + ex.getMessage();
            return false;
        }
        catch (SQLException e){
            ConnectionDbms.message = "Falha na conexão " + ConnectionDbms.getDatabaseName(dbms.getDbms()) 
                                    + ". Erro: " + e.getMessage();
            return false;
        }
    }
    
    public static Connection getConnection(Dbms dbms) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":hmg";
                break;
        }
        Class.forName(driver);
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        return connection;
    }

    public static ResultSet executeQuery(Dbms dbms, String sql) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":hmg";
                break;
        }
        
        Class.forName(driver);
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        connection.close();
        return resultSet;
    }
    
    public static ResultSet executeQuery(Connection connection , String sql) throws SQLException{
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        return resultSet;
    }
    
    public static int executeUpdate(Dbms dbms, String sql) throws ClassNotFoundException, SQLException{
        String driver = "";
        String url = "";
        
        switch(dbms.getDbms()){
            case Dbms.POSTGRESQL:
                driver = ConnectionDbms.driverPostgresql;
                url = "jdbc:postgresql://" + dbms.getServer() + ":" + dbms.getPort() + "/" + dbms.getDatabase();
                break;
            case Dbms.SQLSERVER:
                driver = ConnectionDbms.driverSqlServer;
                url = "jdbc:sqlserver://" + dbms.getServer() + ";database=" + dbms.getDatabase();
                break;
            case Dbms.ORACLE:
                driver = ConnectionDbms.driverOracle;
                url = "jdbc:oracle:thin:@" + dbms.getServer() + ":" + dbms.getPort() + ":hmg";
                break;
        }
        
        Class.forName(driver);
        Connection connection = (Connection) DriverManager.getConnection(url, dbms.getUser(), dbms.getPassword());
        Statement statement = connection.createStatement();
        int rowCount = statement.executeUpdate(sql);
        connection.close();
        return rowCount;
    }
    
    public static int executeUpdate(Connection connection, String sql) throws SQLException{
        Statement statement = connection.createStatement();
        int rowCount = statement.executeUpdate(sql);
        return rowCount;
    }
   
    public static void main(String[] args)
    {
        //System.out.println(ConnectionDbms.hasForingkeyFromTable1InTabel2(new Dbms(Dbms.POSTGRESQL, "localhost", "5432", "teste", "postgres", "123456"), "", "table", "table2", "id1"));
        //System.out.println(ConnectionDbms.isKey(new Dbms(Dbms.POSTGRESQL, "localhost", "5432", "sigaa", "postgres", "123456"), "curriculo", "id_curriculo"));
        //System.out.println(ConnectionDbms.isIndex(new Dbms(Dbms.POSTGRESQL, "localhost", "5432", "sqs", "postgres", "123456"), "query", "text"));
        //System.out.println(ConnectionDbms.getRuntime(new Dbms(Dbms.POSTGRESQL, "localhost", "5432", "tpch", "postgres", "123456"), "select * from customer"));
        //System.out.println(ConnectionDbms.getRuntime(HeuristicSet.SQLSERVER, "select * from customer", "localhost", "1433", "tpch", "sa", "123456"));
        
    }
}  


/*
 * 
idTable1 É UMA CHAVE ESTRANGEIRA NA TABELA table2 DO ESQUEMA public
* 
select * 
from pg_constraint 
where contype='f' and 
      conrelid = (select oid 
		 from pg_class 
		 where relname= 'table2' and 
			relkind = 'r' and 
			relnamespace = (select oid 
					from pg_namespace 
					where UPPER(nspname) = UPPER('public'))) 
      and conkey[1] = (select attnum from pg_attribute where attname = 'idTable1' and 
								attrelid = (select oid 
									    from pg_class 
									    where relname= 'table2' and 
			relkind = 'r' and 
			relnamespace = (select oid 
					from pg_namespace 
					where UPPER(nspname) = UPPER('public'))))
 */